<?php
/*
 * kernel/handlers/database/ibase.php
 *
 * This code is distributed and used under the GNU GPL v2 License,
 * please, read file LICENSE, which is situated in root directory,
 * or in "LICENSE" directory with most acceptable translation for you
 *
 * Interbase/Firebird support
 * 
 * NOTE: For compatibility, please use direct correct sql statements
 * or SqlQuery build class
 *
 * Copyright (c) Taras Kudla, 2010
 */

# last checking: 14.08.2010, status:worked
# tested with Firebird 2.1

class gDatabaseIbase extends gKernel implements gDatabaseConnector{
	private $connection=null;
        private $words = "select,from,table,delete,update,create,alter,where,and,
            or,in,not,as,if,group,limit,having,by,asc,desc,avg,max,count,min,join,
            left,right,inner,outter,set,minimum,maximum,no,first,skip,limit,order,insert,into,values,";
        private $prtmp = array();
        private $prnum = 0;

	# basic ibase object contstruct
	public function init(){
		if(!extension_loaded("interbase")){
			$this->core->error("DBO_ibase","Cannot found ibase PHP module");
		}
	}
	
	function __destruct(){
	
	}

	public function connect($serv,$port,$db,$user,$passwd){
		
		if( strlen($serv)==0 ) $serv="localhost";
		$host=$serv.":".$db;
		
		if($this->core->get("dbopersistent")){
			$c=@ibase_pconnect($host,$user,$passwd,null,null,3);
		}else {
			
			$c=@ibase_connect($host,$user,$passwd,null,null,3);
		}
		if(! is_resource($c)){
			$this->core->error("DBO_DRIVER_CONNECT","CANNOT CONNECT TO THE `ibase` DATABASE SERVER. CHECK PARAMETERS PLEASE.");
		}else{
			$this->connection=&$c;
		}
	}

	public function disconnect(){
		if(is_resource($this->connection)) {
			ibase_close($this->connection);
		}
	}
        # prepare for firebird compatibility
        /*
         * FIXME: NEED CORRECT PARSER
         */
        public function preparesqlcode($sql,$f=false){
            $this->prtmp = array();
            $this->prnum = 0;
            # cutting all data in ' brackets
            $sql = preg_replace_callback("/\'([^']+)\'/s",array(&$this,"prsc"),$sql);
            $sql = preg_replace_callback("/([A-z0-9_@|:\"']+)/s",array(&$this,"psc"),$sql);
            foreach($this->prtmp as $k=>&$v){
                $sql=str_replace($k,$v,$sql);
            }
            return $sql;
        }

        private function psc($val){
            $val = $val[1];
            if( !preg_match("/".$val."\,/i",$this->words) && !preg_match("/'|\"/",$val) && !preg_match("/^\??([0-9]+)$/",$val)){
                return "\"$val\"";
            } else return $val;
        }

        private function prsc($val){
            $pid = "?".$this->prnum.".";
            $this->prtmp[$pid]=$val[0];
            $this->prnum++;
            return $pid;
        }

	# main sql select method with result handling
	public function select($table, $fields = "*", $sql = null, $type = _RESULT_ASSOC){

                
		$limits=null;		
		if( preg_match("/limit\s+([0-9]{1,})\s*((offset|,)\s*([0-9]{1,}))?/i",$sql,$tmp) ){
			$limits = " FIRST ".$tmp[1]." ".(isset($tmp[4])?" SKIP ".$tmp[4]:null);
			$sql = preg_replace("/limit\s+([0-9]{1,})\s*((offset|,)\s*([0-9]{1,}))?/i","",$sql);
                }
		
                $sql = "select $limits " . $fields . " from " . $table . " " . $sql;

                $sql = $this->preparesqlcode($sql);
                
		$q = @ibase_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_SELECT", (is_resource($this->connection) ? pg_last_error($this->connection) : null));
			return false;
		}
		switch ($type){
                    case _RESULT_ASSOC:
                        return $this->sqlToAssoc($q);
                        break;
                    case _RESULT_LIST:
                        return $this->sqlToList($q, $fields);
                        break;
                    case _RESULT_BOOL:
                        return ((bool) ibase_fetch_row($q));
                        break;
                    case _RESULT_PAIRS:
                        return $this->sqlToPairs($q);
                        break;
                    case _RESULT_NUMROW:
                        return $this->sqlToNumRow($q);
                        break;
                    case _RESULT_ROW:
                        return $this->sqlToRow($q);
                        break;
                    case _RESULT_SINGLE:
                    case _RESULT_ONE:
                                return $this->sqlToSingle($q);
                                break;
                    default:
                        return $this->sqlToAssoc($q);
                }
	}
	
	# sql result to simple array ( as list )
	private function &sqlToList(&$q, $fields){
		if (preg_match("/[,]/", $fields, $null)){
			$this->core->error("DBO_DRIVER_SELECT", "Error in results ordering as LIST. Field should be without comas.");
		return false;
		}
		$a = array();
		while($row=ibase_fetch_row($q)){
			$a[] = $row[0];
		}
		return $a;
	}


	private function sqlToSingle(&$q){
    	if( ibase_num_fields($q)== 1 ){
    		$q=ibase_fetch_row($q);
    		return $q[0];
    	}   
    	return null; 
    }

    # sql select result as associative array ( like dictionary in python )
	private function sqlToRow(&$q){
		return ibase_fetch_assoc($q);
	}

	# sql select result as numeric indexes array
	private function sqlToNumRow(&$q){
		return ibase_fetch_row($q);
	}

	# sql select result to array of pairs key=>value
	private function &sqlToPairs(&$q){
		$array = array();
		if (ibase_num_fields($q) != 2){
			$this->core->error("DBO", "Cannot build pairs for result. Fields number must be 2");
		return false;
		}
		while ($row = ibase_fetch_row($q)){
			$array[$row[0]] = $row[1];
		}
		return $array;
	}

	# sql select result to arrays of associative array
	private function &sqlToAssoc(&$q){
		$array = array();
		while ($row = ibase_fetch_assoc($q) ){
			$array[] = $row;
		}
		return $array;
	}

	# inserting records to sql table
	public function insert($table, array $data,$returnid=null){
		if (!is_array($data) || count($data) < 1){
			return false;
		}
                $keys = array();
                foreach( $data as $k=>$v){
                    $keys[]="\"$k\"";
                    //if( preg_match("/(\'[0-9]+\')/s",$v,$tmp)) {
                   //     $data[$k]=$tmp[1];
                   // }
                }
                $vals = array_values($data);
                $i=0;
                while($i<count($vals)){
                    if( !is_int($vals[$i]) || !preg_match("/[0-9]+/s",$vals[$i])){
                        $vals[$i]="'".$vals[$i]."'";
                    }
                    $i++;
                }
                $sql = "INSERT INTO \"".$table."\" (".implode(",",array_values($keys)).") VALUES (".implode(",",$vals).")";
                $q = ibase_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_INSERT", ibase_errormsg() );
		return false;
		}
		return true;
	}

	# updating table
	public function update($table, $sql){
		$sql = $this->preparesqlcode("update ".$table." ".$sql);

		$q = @ibase_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_UPDATE", ibase_errormsg() );
		return false;
		}
		return true;
	}

    # deleting from table  
	public function delete($table, $sql){
		$sql = $this->preparesqlcode("delete from " . $table . " " . $sql);
		$q = @ibase_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_DELETE", ibase_errormsg() );
		}
		return $q;
	}

	# direct sql query
	public function direct($sql){
                //print $sql."<br>";
		$q = @ibase_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_DIRECT", ibase_errormsg() );
			return false;
		}
		return $q;
	}
	
	public function version(){
		
		$data=null;//ibase_server_info($this->connection,IBASE_SVC_SERVER_VERSION);
		return $data;
	}
	
	public function droptable($table){
    		if( is_object($table) && get_class($table)=="sqltable"){
    			$table = $table->getname();
    		}
                //$this->direct("DROP GENERATOR \"GEN_{$table}_id\"");
		$q=$this->direct("DROP TABLE \"$table\"");
                
                ibase_commit($q);
	}
	
	public function createtable(sqltable $map,$returnsql=false){
        $tables = $this->gettables();
        $autoinc = false;
     	list($name,$fields,$keys,$encoding,$engine) = $map->get();    	
      	$pkeys =array();
      	foreach( $keys as $k=>$v ){
      		if( $v[0]==_KEY_PRIMARY) $pkeys[]=$v[1];
      	}
      	$text = "CREATE TABLE \"$name\" ( ";
      	# fields processing      	
      	foreach( $fields as $k=>$v){
      		list($fname,$ftype,$fsize,$fdefault) = $v;
      		$fresult = $this->types($ftype,$fsize);
                if( $ftype == _FIELD_BIGINTAUTO || $ftype==_FIELD_INTAUTO){
                    $autoinc = $fname;
                }
      		if( $ftype==_FIELD_TEXT && is_null($fdefault)) {
      			$default = " ";
      		} else if( ( $ftype==_FIELD_INT || $ftype==_FIELD_BIGINT || $ftype==_FIELD_TINYINT )  && is_null($fdefault)){
      			$default =" DEFAULT '0' ";
      		}  else {
      			$default =  !is_null($fdefault)?" DEFAULT '".$this->core->escapeString($fdefault)."' ":($ftype==_FIELD_INTAUTO?null:" NOT NULL ");
      		}		
      		$text .=($k==0?null:" ,")." \"$fname\" $fresult ".($default).( in_array($fname,$pkeys)?" PRIMARY KEY ":null ) ." ";
      	}
      	$text .=" ) ";
      	if( $map->droponexist){
                if( in_array($name,$tables)){
                    $this->direct("DROP TABLE \"$name\"");
                }
      	}
        if( !$returnsql){
            $this->direct($text);
            if( $autoinc){
                $genname = "GEN_".$name."_".$autoinc;
                //$this->direct("CREATE GENERATOR \"$genname\";");
                //$this->direct("SET GENERATOR \"$genname\" TO 0;");
                # creating a trigger
                /*$this->direct("CREATE TRIGGER \"".$name."_AI\" FOR \"$name\"
                ACTIVE BEFORE INSERT POSITION 0
                AS
                BEGIN
                if (NEW.\"$autoinc\" is NULL) then NEW.\"$autoinc\" = GEN_ID(\"$genname\", 1);
                END");*/
            }
        }

      	# keys processing
        $query = null;
      	foreach( $keys as $k=>$v){
      		$query = null;
      		list($ktype,$kfield) = $v;
      		$keydata = null;      		
      		switch( strtolower($ktype) ){
      			case _KEY_INDEX  : $keydata = "INDEX"; break;
      			case _KEY_UNIQUE : $keydata = "UNIQUE INDEX"; break;
  			
      		}    		
      		if( !is_null($keydata)){
      			
      			$query = "CREATE $keydata \"$name"."_"."$kfield\" ON \"$name\" ( \"".$kfield."\" ) ";
                        if( !$returnsql)
                            $this->direct($query);
      			
      		}
      	}
      	return $returnsql?$text.$query:true;
	}
	
	private function types($type,$size=null){

		$result = null;
                switch ($type)
                {
                    case _FIELD_INTAUTO:
                        $result = " INTEGER ";
                        break;
                    case _FIELD_BIGINTAUTO:
                        $result = " INTEGER ";
                        break;
                    case _FIELD_INT:
                        $result = " integer ";
                        break;
                    case _FIELD_BIGINT:
                        $result = " BIGINT ";
                        break;
                    case _FIELD_TINYINT:
                        $result = " SMALLINT ";
                        break;
                    case _FIELD_SMALLINT:
                        $result = " SMALLINT ";
                        break;
                    case _FIELD_CHAR:
                        $result = "VARCHAR(" . ($size != null ? (int) $size : "250") . ") ";
                        break;
                    case _FIELD_CONSTCHAR:
                        $result = "char(" . ($size != null ? (int) $size : "250") . ") ";
                        break;
                    case _FIELD_DATE:
                        $result = "DATE";
                        break;
                    case _FIELD_DATETIME:
                        $result = "TIMESTAMP";
                        break;
                    case _FIELD_TIMESTAMP:
                        $result = "TIMESTAMP";
                        break;
                    case _FIELD_TIME:
                        $result = "TIME";
                        break;
                    case _FIELD_FLOAT:
                        $result = "FLOAT";
                        break;
                    case _FIELD_REAL:
                        $result = "REAL";
                        break;
                    case _FIELD_DOUBLE:
                        $result = " DOUBLE PRECISION";
                        break;
                    case _FIELD_DECIMAL:
                        $result = "DECIMAL(".($size != null ? $size : "10,5").") ";
                        break;
                    case _FIELD_TEXT:
                        $result = "VARCHAR(8000) ";
                        break;
                    case _FIELD_LONGTEXT:
                        $result = "VARCHAR(8000) ";
                        break;
                    case _FIELD_BIT:
                        $result = " CHAR(1) "; /* !!!!!!!!!! */
                        break;
                    case _FIELD_BOOL:
                        $result = " SMALLINT ";
                        break;
                    case _FIELD_BLOB:
                        $result = "BLOB";
                        break;
                    default:
                        "character varying(255)";
                        break;
                }
        return $result;
	}
        
	public function tableexists($tablename){
	
	}
        public function  getdatabases() {

        }

        public function  getdatabase() {

        }
        public function  getfields($table) {

        }
        public function  gettables($database = null) {
            $query = 'select rdb$relation_name from rdb$relations
where rdb$view_blr is null
and (rdb$system_flag is null or rdb$system_flag = 0)';
            $q = ibase_query($this->connection,$query);
            $a = array();
            while($row=ibase_fetch_row($q)){
                $a[] = trim($row[0]);
            }
            return $a;
        }
        public function  execute($sql) {
            ibase_query($this->connection,$sql);
        }
}